package orest.back.membermanage;

import java.sql.Connection;
import java.sql.SQLException;

import net.ssta.common.Constants;
import net.ssta.core.db.DBConnectionProvider;
import net.ssta.core.db.DataBaseExecutor;
import net.ssta.core.db.PagingInfo;
import net.ssta.core.db.Record;
import net.ssta.core.db.RecordSet;

public class UserManagerSql {
	
	
	public static String userInfoList(Record searchData,PagingInfo pageingInfo){
		StringBuffer sql=new StringBuffer();
		
		
		
		sql.append(" SELECT   t.UserId,t.LoginName,t.UserName,p.USERPOINT,p.STARTDATE,")
		   .append(" p.ENDDATE,u.Sex,u.FullName,u.Email,u.Birthday,u.Telephone ");
    	sql.append(" FROM V_REGISTER_USER WHERE NEITYPE='1' ");
    	String loginName=searchData.getString("LOGINNAME","");
    	String userName=searchData.getString("USERNAME","");
    	if(!"".equals(loginName)){
    		sql.append(" AND loginName like '%").append(loginName).append("%' ");
    	}
    	if(!"".equals(userName)){
    		sql.append(" AND UserName like '%").append(userName).append("%' ");
    	}
	   
    	return sql.toString();
	}
	
	
	
	/**
	 * 
	 * @param searchData
	 * @param pageingInfo
	 * @return
	 */
	public static String orderList(Record searchData,PagingInfo pageingInfo){
		StringBuffer sql=new StringBuffer();
		
		
		sql.append(" SELECT  o.*,u.LoginName,u.FULLNAME,u.TELEPHONE,u.EMail ");
		sql.append(" FROM   T_USER_ORDER o Left join V_REGISTER_USER u on o.USERID=u.USERID  WHERE  1=1 ");
		
		String userId=searchData.getString("USERID","");
		if(!userId.equals("")){
			sql.append(" AND o.USERID='").append(userId).append("' ");
		}
		if(!searchData.getString("ORDERID","").equals("")){
			sql.append(" AND o.ORDERID like '%").append(searchData.getString("ORDERID")).append("%' ");
		}
		
		if(!searchData.getString("USERNAME","").equals("")){
			sql.append(" AND u.LoginName like '%").append(searchData.getString("USERNAME")).append("%' ");
		}
		if(!searchData.getString("ISFINISH","").equals("")){
			sql.append(" AND o.ISFINISH ='").append(searchData.getString("ISFINISH")).append("' ");
		}
		sql.append(" ORDER BY ORDERTIME DESC" );
	 
    	return sql.toString();
	}
	
	
	/**
	 * 
	 * @param searchData
	 * @param pageingInfo
	 * @return
	 */
	public static String costRecordList(Record searchData,PagingInfo pageingInfo){
		 
	 
		
		
		StringBuffer sql=new StringBuffer();
		
		
		sql.append(" SELECT b.*,v.LOGINNAME,v.FULLNAME,v.TELEPHONE,v.EMAIL ");
		sql.append(" FROM   V_BILL_INFORMATION b left join V_REGISTER_USER v on b.COSTUSERNAME=v.USERID WHERE 1=1 ");
		
		String userId=searchData.getString("USERID","");
		if(!userId.equals("")){
			sql.append(" AND COSTUSERNAME='").append(userId).append("' ");
		}
		
		String userName=searchData.getString("USERNAME","");
		String startDate=searchData.getString("STARTDATE","");
		String endDate=searchData.getString("ENDDATE","");
		
		if(!userName.equals("")){
			sql.append(" AND v.LOGINNAME like'%").append(userName).append("%' ");
		}
		if(!startDate.equals("")){
			sql.append(" AND COSTTIME >='").append(startDate).append("' ");
		}
		if(!endDate.equals("")){
			sql.append(" AND COSTTIME <='").append(endDate).append("' ");
		}
		 
		sql.append(" ORDER BY COSTTIME DESC" );
		
		                      
				
	 
    	
    	
    	return sql.toString();
	}
	
	
	/**
	 * 
	 * @param searchData
	 * @param pageingInfo
	 * @return
	 */
	public static String reChargeList(Record searchData,PagingInfo pageingInfo){
		StringBuffer sql=new StringBuffer();
		
		
		sql.append(" SELECT b.*,v.LOGINNAME,v.FULLNAME,v.TELEPHONE,v.EMAIL,u.LoginName as OperatorLoginName,u.FullName as OperatorFullName ");
		sql.append(" FROM   T_USER_BILL b left join V_REGISTER_USER u on b.operator = u.userid ")
		   .append(" left join V_REGISTER_USER v on b.COSTUSERNAME=v.USERID WHERE  (CTYPE = 'recharge' OR CTYPE = 'vip' or CTYPE='alipay') ");
		
		String userId=searchData.getString("USERID","");
		if(!userId.equals("")){
			sql.append(" AND COSTUSERNAME='").append(userId).append("' ");
		}
		
		String userName=searchData.getString("USERNAME","");
		String startDate=searchData.getString("STARTDATE","");
		String endDate=searchData.getString("ENDDATE","");
		
		if(!userName.equals("")){
			sql.append(" AND v.LOGINNAME like'%").append(userName).append("%' ");
		}
		if(!startDate.equals("")){
			sql.append(" AND COSTTIME >='").append(startDate).append("' ");
		}
		if(!endDate.equals("")){
			sql.append(" AND COSTTIME <='").append(endDate).append("' ");
		}
		 
		sql.append(" ORDER BY COSTTIME DESC" );
		
		                      
				
	 
    	return sql.toString();
	}
	
	
	
	

	/**
	 * 
	 * @param searchData
	 * @param pageingInfo
	 * @return
	 */
	public static String applyList(Record searchData){
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT    u.*,p.FKID,p.FKTYPE,APPLYNAME,p.MARK,e.Item_180 as expertName,un.Item_9 AS unitName");
		sql.append(" FROM  T_USERUPGRADE p  ");
		sql.append(" inner join V_REGISTER_USER u on u.USERID=p.USERID ");
		sql.append(" left join T_Expert e on p.FKID=e.ExpertID");
		sql.append(" left join T_Unit un on p.FKID=un.UnitID  ");
		sql.append(" WHERE 1=1 ");
		String loginName=searchData.getString("LOGINNAME","");
		String fullName=searchData.getString("FULLNAME","");
		String fkType=searchData.getString("FKTYPE","");
		
		if(!loginName.equals("")){
			sql.append(" AND u.loginName like '%").append(loginName).append("%' ");
		}
		if(!fullName.equals("")){
			sql.append(" AND u.fullName like '%").append(fullName).append("%' ");
		}
		if(!fkType.equals("")){
			sql.append(" AND p.fkType ='").append(fkType).append("' ");
		}
		sql.append(" ORDER BY APPTIME desc,p.FKID");
    	return sql.toString();
	}
	
	
	
	
	
	/**
	 * 
	 * @param searchData
	 * @param pageingInfo
	 * @return
	 */
	public static String unitList(Record searchData){
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT  UnitID,Item_9,Item_37,Item_39 ");
		sql.append(" FROM  T_UNIT u   ");
		
		String unitName=searchData.getString("UNITNAME","");
		
		if(!unitName.equals("")){
			sql.append(" WHERE u.Item_9 like '%").append(unitName).append("%' ");
		}
		 
    	return sql.toString();
	}
	
	
	/**
	 * 
	 * @param searchData
	 * @param pageingInfo
	 * @return
	 */
	public static String expertList(Record searchData){
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT  e.ExpertID,e.Item_180,e.Item_182,e.WorkSpeciality,u.Item_9 as unitName");
		sql.append(" FROM  T_Expert e left join  T_UNIT u  on e.UnitID=u.UnitID ");
		
		String expertName=searchData.getString("EXPERTNAME","");
		
		if(!expertName.equals("")){
			sql.append(" WHERE e.Item_180 like '%").append(expertName).append("%' ");
		}
		 
    	return sql.toString();
	}
}
